#! /usr/bin/env python3
# -*- coding: utf-8 -*-
# :CREATED: [2018-04-20 Fri 21:45]
# :AUTHOR: zb
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

dbpath = 'sqlite:///sqlalchemy_example.db'
engine = create_engine(dbpath, echo=True)
Session = sessionmaker(bind=engine)
db = Session()


class Models(object):
    @classmethod
    def new(cls, **form):

        return cls(**form)

    @classmethod
    def get(cls, pk):
        '''
        Get by primary key.
        '''
        o = db.query(cls).get(pk)
        return o

    @classmethod
    def find_first(cls, **args):
        '''
        Find by where clause and return one result. If multiple results found, 
        only the first one returned. If no result found, return None.
        '''
        o = db.query(cls).filter_by(**args).first()
        return o

    @classmethod
    def find_all(cls):
        '''
        Find all and return list.
        '''
        o = db.query(cls).all()
        return o

    @classmethod
    def find_by(cls, **args):
        '''
        Find by where clause and return list.
        '''
        o = db.query(cls).filter_by(**args).all()
        return o

    @classmethod
    def count_all(cls):
        '''
        Find by 'select count(pk) from table' and return integer.
        '''
        return db.query(cls).count()

    @classmethod
    def count_by(cls, **args):
        '''
        Find by 'select count(pk) from table where ... ' and return int.
        '''
        return db.query(cls).filter_by(**args).count()

    @classmethod
    def delete(cls):
        db.delete(cls)
        return cls

    @classmethod
    def delete_by(cls, **args):
        db.query(cls).filter(**args).delete()
        return cls

    @classmethod
    def insert(cls, t):
        '''
        insert one 
        :param t: 
        :return: 
        '''
        db.add(t)
        return cls

    @classmethod
    def insert_all(cls, t):
        '''
        batch insert
        :param t:  list
        :return: 
        '''
        db.add_all(t)
        return cls

    @classmethod
    def commit(self):
        db.commit()
        db.flush()

    @classmethod
    def rollback(self):
        db.rollback()

    def update(self, **args):
        for k, v in args.items():
            setattr(self, k, v)
        return self

    def __repr__(self):
        """
        return all property of instance
        """
        classname = self.__class__.__name__
        properties = [
            '{}: ({})'.format(k, v) for k, v in self.__dict__.items()
        ]
        s = '\n'.join(properties)
        return '< {}\n{} >\n'.format(classname, s)
